/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLager;
import ModelLager.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Ronni
 */
public class DbProductP implements IFDbProductP{
    private Connection con;
    private String dbName;

    public DbProductP() {
        con = DbConnection2.getInstance().getDBcon();
        dbName = "ProductP";
    }
   
    @Override
    public int deleteProduct(ProductP product)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        PreparedStatement preStmnt = null;
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        try{
                query = "DELETE FROM " +dbName+ " WHERE productID = ?";
                preStmnt = con.prepareStatement(query);
                preStmnt.setInt(1, product.getProductID());
                rc = preStmnt.executeUpdate();
                preStmnt.close();
        }catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Fejl: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception " + e.getMessage());
        }        
        return rc;
        
    }
    @Override
    public int insertProduct(ProductP product)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        PreparedStatement preStmnt = null;
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        
        //fordi con.prepareStatement metoden "kaster" en fejl fejl(SQLException)
        //skal koden indrammes i try/catch
        try
        {
                query = "INSERT INTO " +dbName+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                preStmnt = con.prepareStatement(query);
                preStmnt.setString(1, product.getName());
                preStmnt.setDouble(2, product.getPurchasePrice());
                preStmnt.setDouble(3, product.getSalesPrice());
                preStmnt.setDouble(4, product.getRentPrice());
                preStmnt.setString(5, product.getCountryOfOrigin());
                preStmnt.setInt(6, product.getMinStock());
                preStmnt.setInt(7, product.getBarCode());
                preStmnt.setInt(8, product.getProductID());
                preStmnt.setInt(9, product.getSupplierID());
                preStmnt.setString(10, product.getType());
                preStmnt.setString(11, product.getDescription());
                preStmnt.setString(12, product.getSize());
                preStmnt.setString(13, product.getColor());
                preStmnt.setString(14, product.getFabric());
                preStmnt.setString(15, product.getDescriptionGun());
                rc = preStmnt.executeUpdate();   
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println(SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println(e.getMessage());
        }        
        return rc;

    }
    @Override
    public int updateProduct(ProductP product)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        
        //fordi con.prepareStatement metoden "kaster" en fejl fejl(SQLException)
        //skal koden indrammes i try/catch
        try
        {
        
query = "UPDATE " +dbName+ " SET name = ?, purchasePrice = ?,salesPrice = ?,rentPrice = ?,countryOfOrigin = ?,"
        + "minStock = ?,barCode = ?,productID = ?, supplierID = ?,type = ?,descriptionEquip = ?,size = ?,colour = ?, fabric = ?, descriptionGun = ? WHERE productID = " + product.getProductID();

PreparedStatement preStmnt = con.prepareStatement(query);
System.out.println(query);
                preStmnt.setString(1, product.getName());
                preStmnt.setDouble(2, product.getPurchasePrice());
                preStmnt.setDouble(3, product.getSalesPrice());
                preStmnt.setDouble(4, product.getRentPrice());
                preStmnt.setString(5, product.getCountryOfOrigin());
                preStmnt.setInt(6, product.getMinStock());
                preStmnt.setInt(7, product.getBarCode());
                preStmnt.setInt(8, product.getProductID());
                preStmnt.setInt(9, product.getSupplierID());
                preStmnt.setString(10, product.getType());
                preStmnt.setString(11, product.getDescription());
                preStmnt.setString(12, product.getSize());
                preStmnt.setString(13, product.getColor());
                preStmnt.setString(14, product.getFabric());
                preStmnt.setString(15, product.getDescriptionGun());
                
                
                rc = preStmnt.executeUpdate();
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());
        }        
        return rc;

    }
    
    
    @Override
    public ProductP findProduct(int productID)
    {      
            PreparedStatement preStmnt =  getPreparedStmnt();          
            ProductP pObj = null;
            ResultSet results;
            try{    
            preStmnt.setInt(1,productID);                
            results = preStmnt.executeQuery();
            if(results.next()){
                 pObj = buildProduct(results);	
            }
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findProduct(): " + e.getMessage());
                
            }
            
            return pObj;
        
    }
    @Override
    public ArrayList<ProductP> findProducts(String name)
    {      
            PreparedStatement preStmnt = null;          
            ArrayList<ProductP> list = new ArrayList<ProductP>();
            ResultSet results;
            ProductP pObj = null;
            try{    
            String query = "SELECT * FROM " + dbName + " WHERE name like '%" +name+"%' OR productID like '%" + name + "%'";     
            preStmnt = con.prepareStatement(query);
//            int i = Integer.parseInt(name);
//            preStmnt.setInt(1, i);
            results = preStmnt.executeQuery();
            
            while(results.next()){
                 pObj = buildProduct(results);
                 list.add(pObj);	
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findAllProduct(): " + e.getMessage());
                
            }
            
            return list;
        
    }    
    
    private PreparedStatement getPreparedStmnt()
    {
        PreparedStatement preStmnt = null;
        try{
        String query = "SELECT * FROM "+dbName+" WHERE productID = ?";
        preStmnt = con.prepareStatement(query);
        }catch(Exception e)
        {
            System.out.println("Error in getpreparedstatement--> " + e.getMessage());
        }
        return preStmnt;
    }
    
    
        private ProductP buildProduct(ResultSet results)
    {
       ProductP proObj = new ProductP();
       
         try{
                        
                proObj.setName(results.getString("name"));
                proObj.setPurchasePrice(results.getDouble("purchasePrice"));
                proObj.setSalesPrice(results.getDouble("salesPrice"));
                proObj.setRentPrice(results.getDouble("rentPrice"));
                proObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
                proObj.setMinStock(results.getInt("minStock"));
                proObj.setBarCode(results.getInt("barCode"));
                proObj.setProductID(results.getInt("productID"));
                proObj.setSupplierID(results.getInt("supplierID"));
                proObj.setType(results.getString("type"));
                proObj.setDescription(results.getString("descriptionEquip"));
                proObj.setSize(results.getString("size"));
                proObj.setColor(results.getString("colour"));
                proObj.setFabric(results.getString("fabric"));
                proObj.setDescriptionGun(results.getString("descriptionGun"));
               
          }
         catch(Exception e)
         {
             System.out.println("error building project object: " + e.getMessage());
         }
        
        return proObj;
        
    }
  
}